using System.Data;
using Microsoft.Data.SqlClient;
using Oracle.ManagedDataAccess.Client;

namespace Lzfy_Refund_Service.Services
{
    /// <summary>
    /// 数据库健康检查服务
    /// </summary>
    public class DatabaseHealthCheckService : BackgroundService
    {
        private readonly ILogger<DatabaseHealthCheckService> _logger;
        private readonly IDatabaseConfigurationService _dbConfigService;
        private readonly IServiceProvider _serviceProvider;
        private readonly TimeSpan _checkInterval = TimeSpan.FromMinutes(2); // 每2分钟检查一次
        private readonly int _maxRetryAttempts = 3;
        private readonly TimeSpan _retryDelay = TimeSpan.FromSeconds(5);
        
        // 数据库健康状态
        private bool _sqlServerHealthy = true;
        private bool _oracleHealthy = true;
        
        // 上次检查时间
        private DateTime _lastSqlServerCheckTime = DateTime.MinValue;
        private DateTime _lastOracleCheckTime = DateTime.MinValue;

        public DatabaseHealthCheckService(
            ILogger<DatabaseHealthCheckService> logger,
            IDatabaseConfigurationService dbConfigService,
            IServiceProvider serviceProvider)
        {
            _logger = logger;
            _dbConfigService = dbConfigService;
            _serviceProvider = serviceProvider;
        }

        protected override async Task ExecuteAsync(CancellationToken stoppingToken)
        {
            _logger.LogInformation("数据库健康检查服务启动");

            while (!stoppingToken.IsCancellationRequested)
            {
                try
                {
                    await CheckDatabasesHealthAsync();
                }
                catch (Exception ex)
                {
                    _logger.LogError(ex, "执行数据库健康检查时发生异常");
                }

                try
                {
                    await Task.Delay(_checkInterval, stoppingToken);
                }
                catch (OperationCanceledException)
                {
                    // 正常取消，退出循环
                    break;
                }
            }

            _logger.LogInformation("数据库健康检查服务停止");
        }

        /// <summary>
        /// 检查所有数据库的健康状态
        /// </summary>
        private async Task CheckDatabasesHealthAsync()
        {
            // 检查SQL Server数据库
            var sqlServerResult = await CheckSqlServerHealthAsync();
            if (_sqlServerHealthy != sqlServerResult)
            {
                _sqlServerHealthy = sqlServerResult;
                if (_sqlServerHealthy)
                {
                    _logger.LogInformation("SQL Server数据库连接恢复正常");
                }
                else
                {
                    _logger.LogWarning("SQL Server数据库连接异常");
                }
            }

            // 检查Oracle数据库
            var oracleResult = await CheckOracleHealthAsync();
            if (_oracleHealthy != oracleResult)
            {
                _oracleHealthy = oracleResult;
                if (_oracleHealthy)
                {
                    _logger.LogInformation("Oracle数据库连接恢复正常");
                }
                else
                {
                    _logger.LogWarning("Oracle数据库连接异常");
                }
            }

            // 更新最后检查时间
            _lastSqlServerCheckTime = DateTime.Now;
            _lastOracleCheckTime = DateTime.Now;
        }

        /// <summary>
        /// 检查SQL Server数据库健康状态
        /// </summary>
        private async Task<bool> CheckSqlServerHealthAsync()
        {
            var connectionString = _dbConfigService.GetDefaultConnection();
            return await CheckDatabaseConnectionAsync(connectionString, isSqlServer: true);
        }

        /// <summary>
        /// 检查Oracle数据库健康状态
        /// </summary>
        private async Task<bool> CheckOracleHealthAsync()
        {
            var connectionString = _dbConfigService.GetHisOracleConnection();
            return await CheckDatabaseConnectionAsync(connectionString, isSqlServer: false);
        }

        /// <summary>
        /// 检查数据库连接
        /// </summary>
        private async Task<bool> CheckDatabaseConnectionAsync(string connectionString, bool isSqlServer)
        {
            for (int attempt = 1; attempt <= _maxRetryAttempts; attempt++)
            {
                try
                {
                    if (isSqlServer)
                    {
                        using var sqlConnection = new SqlConnection(connectionString);
                        await sqlConnection.OpenAsync();
                        
                        // 执行简单查询验证连接
                        using var command = sqlConnection.CreateCommand();
                        command.CommandText = "SELECT 1";
                        command.CommandTimeout = 5; // 设置较短的超时时间
                        
                        var result = await command.ExecuteScalarAsync();
                        return true;
                    }
                    else
                    {
                        using var oracleConnection = new OracleConnection(connectionString);
                        await oracleConnection.OpenAsync();
                        
                        // 执行简单查询验证连接
                        using var command = oracleConnection.CreateCommand();
                        command.CommandText = "SELECT 1 FROM DUAL";
                        command.CommandTimeout = 5; // 设置较短的超时时间
                        
                        var result = await command.ExecuteScalarAsync();
                        return true;
                    }
                }
                catch (Exception ex)
                {
                    var dbType = isSqlServer ? "SQL Server" : "Oracle";
                    _logger.LogWarning(ex, "{DbType}数据库连接检查失败 (尝试 {Attempt}/{MaxAttempts}): {Message}", 
                        dbType, attempt, _maxRetryAttempts, ex.Message);
                    
                    if (attempt < _maxRetryAttempts)
                    {
                        await Task.Delay(_retryDelay);
                    }
                }
            }
            
            return false;
        }

        /// <summary>
        /// 获取数据库健康状态
        /// </summary>
        public (bool SqlServerHealthy, bool OracleHealthy, DateTime LastSqlServerCheckTime, DateTime LastOracleCheckTime) GetDatabasesHealth()
        {
            return (_sqlServerHealthy, _oracleHealthy, _lastSqlServerCheckTime, _lastOracleCheckTime);
        }
    }
}